//    Copyright (C) Kherty.  All rights reserved.
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Diagnostics.Contracts;
using OpenLS.Spreadsheet.Extensibility;
using OpenLS.Spreadsheet.StandardAddIn.Statistics;

// ReSharper disable UnusedMember.Global

namespace OpenLS.Spreadsheet.StandardAddIn
{
    [AddInFunctions]
    public static class StatisticalFunctions
    {
        private const double Epsilon = 0.00000000001;

        public static object BetaDist(double x, double alpha, double beta, [Optional(0d)] double A,
                                      [Optional(1d)] double B)
        {
            if (alpha == 0)
                return Errors.NumError;
            if (beta == 0)
                return Errors.NumError;
            if (x < A)
                return Errors.NumError;
            if (x > B)
                return Errors.NumError;
            if (A == B)
                return Errors.NumError;
            var d = new BetaDistribution(alpha, beta);
            return d.CumulativeProbability((x - A)/(B - A));
        }

        public static object BetaInv(double probability, double alpha, double beta, [Optional(0d)] double A,
                                     [Optional(1d)] double B)
        {
            if (alpha == 0)
                return Errors.NumError;
            if (beta == 0)
                return Errors.NumError;
            if (!Distribution.IsValidProbabilty(probability))
                return Errors.NumError;
            if (probability == 0 || probability == 1)
                return Errors.NumError;
            if (A == B)
                return Errors.NumError;
            var d = new BetaDistribution(alpha, beta);
            return A + (B - A)*d.InverseCumulativeProbability(probability);
        }

        public static object BinomDist(double number_s, int trials, double probability_s,
                                       [TreatZeroEmptyAsFalse] bool cumulative)
        {
            if (!Distribution.IsValidProbabilty(probability_s) ||
                !BinomialDistribution.IsValidTrialCount(trials))
                return Errors.NumError;

            var d = new BinomialDistribution(trials, probability_s);
            if (cumulative)
                return d.CumulativeProbability(number_s);
            else
                return d.Probability(number_s);
        }

        public static object ChiDist(double x, double deg_freedom)
        {
            if (deg_freedom < 1)
                return Errors.NumError;
            if (x < 0)
                return Errors.NumError;
            return 1 - new ChiSquaredDistribution(deg_freedom).CumulativeProbability(x);
        }

        public static object ChiInv(double probability, double deg_freedom)
        {
            if (deg_freedom < 1)
                return Errors.NumError;
            return new ChiSquaredDistribution(deg_freedom).InverseCumulativeProbability(1 - probability);
        }

        [AllowCellToRangeConversion]
        public static object ChiTest(IArray actual_range, IArray expected_range)
        {
            if (actual_range.ColumnCount != expected_range.ColumnCount ||
                actual_range.RowCount != expected_range.RowCount)
                return Errors.NaError;
            if (actual_range.RowCount*actual_range.ColumnCount < 2)
                return Errors.NaError;
            var actual = new List<double>();
            var expected = new List<double>();
            for (int i = 0; i < actual_range.RowCount; i++)
            {
                for (int j = 0; j < actual_range.ColumnCount; j++)
                {
                    object a = actual_range[i, j];
                    object e = expected_range[i, j];
                    if (a is double && e is double)
                    {
                        actual.Add((double) a);
                        expected.Add((double) e);
                    }
                }
            }
            var Actual = new double[actual.Count];
            actual.CopyTo(Actual);
            var Expected = new double[expected.Count];
            expected.CopyTo(Expected);
            int c = actual_range.RowCount;
            if (c == 1)
                c = actual_range.ColumnCount;
            return new ChiSquareTest().Test(Expected, Actual, c - 1);
        }

        public static object Confidence(double alpha, double standard_dev, int size)
        {
            if (size < 0 || standard_dev <= 0)
                return Errors.NumError;
            double probability = 1 - alpha/2;
            if (!Distribution.IsValidProbabilty(probability))
                return Errors.NumError;
            double d = new NormalDistribution(0, 1).InverseCumulativeProbability(probability);
            return d*standard_dev/Math.Sqrt(size);
        }

        [IgnoreBooleans, IgnoreNulls, IgnoreStrings]
        public static object Correl(double[] array1, double[] array2)
        {
            if (array1.Length != array2.Length)
                return Errors.NaError;
            var l = new List<double>();
            l.AddRange(array1);
            var l2 = new List<double>();
            l2.AddRange(array2);
            var r = new SimpleRegression(l, l2, RegressionType.WithIntercept);
            return r.Correlation;
        }

        public static double CritBinom(double trials, double probability_s, double alpha)
        {
            Contract.Ensures(Contract.Result<double>() >= 0);
            if (trials < 0 || probability_s < 0 || probability_s > 1 || alpha < 0 || alpha > 1)
                throw new ErrorException(Errors.NumError);
            var d = new BinomialDistribution((int) trials, probability_s);
            int min = 0;
            int max = int.MaxValue;
            double minValue = d.CumulativeProbability(min);
            if (minValue >= alpha)
                return min;
            double maxValue = d.CumulativeProbability(max);
            if (maxValue <= alpha)
                return max;
            while (max - min > 1)
            {
                int mid = (max + min)/2;
                double midValue = d.CumulativeProbability(mid);
                if (midValue < alpha)
                {
                    min = mid;
                }
                else
                {
                    max = mid;
                }
            }
            return max;
        }

        public static object DevSq(
            [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreNulls, IgnoreStrings(ReferenceOption.ReferenceOnly),
             TreatBooleansAsDouble(ReferenceOption.ArgumentOnly),
             AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly)
            ] params double[] number)
        {
            if (number.Length == 0)
                return Errors.NumError;
            double m = Statistician.Mean(number);
            double result = 0;
            foreach (double d in number)
            {
                double dif = d - m;
                result += dif*dif;
            }

            return result;
        }

        [AddInFunction(Name = "EXPON.DIST")]
        public static object Expon_Dist(double x, double lambda, [TreatZeroEmptyAsFalse] bool cumulative)
        {
            return ExponDist(x, lambda, cumulative);
        }

        public static object ExponDist(double x, double lambda, [TreatZeroEmptyAsFalse] bool cumulative)
        {
            if (lambda <= 0)
                return Errors.NumError;
            var d = new ExponentialDistribution(lambda);
            if (cumulative)
                return d.CumulativeProbability(x);
            else
                return d.Probability(x);
        }

        public static object FDist(double x, double deg_freedom1, double deg_freedom2)
        {
            if (deg_freedom1 < 1 || deg_freedom2 < 1)
                return Errors.NumError;

            var d = new FDistribution(deg_freedom1, deg_freedom2);
            return 1 - d.CumulativeProbability(x);
        }

        public static object FInv(double probability, double deg_freedom1, double deg_freedom2)
        {
            if (deg_freedom1 < 1 || deg_freedom2 < 1)
                return Errors.NumError;
            var d = new FDistribution(deg_freedom1, deg_freedom2);
            return d.InverseCumulativeProbability(1 - probability);
        }

        public static object Fisher(double x)
        {
            if (x <= -1 || x >= 1)
                return Errors.NumError;
            return Math.Log((x + 1)/(1 - x))/2;
        }

        public static double FisherInv(double y)
        {
            return (Math.Exp(2*y) - 1)/(Math.Exp(2*y) + 1);
        }

        public static object Forecast([WorksheetContext] IWorksheet worksheet, double x, IArray known_y, IArray known_x)
        {
            List<double> ys;
            List<double> xs;
            ExtractValues(known_x, known_y, out xs, out ys, worksheet.Workbook);
            if (xs.Count != ys.Count)
                return Errors.NumError;
            if (xs.Count == 0)
                return Errors.NumError;
            double averageXs = 0;
            foreach (double xx in xs)
                averageXs += xx;
            averageXs = averageXs/xs.Count;
            double averageYs = 0;
            foreach (double yy in ys)
                averageYs += yy;
            averageYs = averageYs/xs.Count;
            double den = 0;
            foreach (double xx in xs)
                den += (xx - averageXs)*(xx - averageXs);
            if (den == 0)
                return Errors.NumError;
            double num = 0;
            for (int i = 0; i < xs.Count; i++)
            {
                num += (xs[i] - averageXs)*(ys[i] - averageYs);
            }
            double b = num/den;
            double a = averageYs - b*averageXs;
            return a + b*x;
        }

        internal static void ExtractValues(IArray values, IArray values2, out List<double> outValues1,
                                           out List<double> outValues2, IWorkbook workbook)
        {
            outValues1 = new List<double>();
            outValues2 = new List<double>();
            if (values2.RowCount != values.RowCount || values2.ColumnCount != values.ColumnCount)
                throw new ErrorException(Errors.NaError);
            for (int row = 0; row < values.RowCount; row++)
            {
                for (int column = 0; column < values.ColumnCount; column++)
                {
                    object o1 = values[row, column];
                    object o2 = values2[row, column];
                    if (o1 == null || o2 == null)
                        continue;
                    if (o1 is Error)
                        throw new ErrorException(((Error)o1));
                    if (o2 is Error)
                        throw new ErrorException(((Error)o2));
                    if (o1 is DateTime)
                        o1 = workbook.DateTimeToDouble((DateTime) o1);
                    if (o2 is DateTime)
                        o2 = workbook.DateTimeToDouble((DateTime) o2);

                    if (o1 is double && o2 is double)
                    {
                        outValues1.Add((double) o1);
                        outValues2.Add((double) o2);
                    }
                    else
                    {
                        // Contract.Assert(false);
                    }
                }
            }
        }

        private static List<double> ExtractValues(IArray values)
        {
            var result = new List<double>();
            for (int row = 0; row < values.RowCount; row++)
            {
                for (int column = 0; column < values.ColumnCount; column++)
                {
                    result.Add((double)values[row, column]);
                }
            }
            return result;
        }
        private static List<List<double>> ExtractValues2(IArray values)
        {
            var result = new List<List<double>>();
            for (int row = 0; row < values.RowCount; row++)
            {
                List<double> l = new List<double>();
                result.Add(l);
                for (int column = 0; column < values.ColumnCount; column++)
                {
                    l.Add((double)values[row, column]);
                }
            }
            return result;
        }


        public static object Frequency(IArray data_array, IArray bins_array)
        {
            List<double> values = ExtractValues(data_array);
            List<double> bins = ExtractValues(bins_array);
            double[] result = FrequencyImp(values, bins);
            var temp = new List<object>();
            foreach (double d in result)
                temp.Add(d);
            return new RowArrayValue(temp);
        }

        private static double[] FrequencyImp(List<double> values, List<double> bins_array)
        {
            var result = new double[bins_array.Count + 1];
            bins_array.Sort();
            var bins = new Bin[bins_array.Count + 1];
            bins[0] = new Bin(double.MinValue, bins_array[0]);
            for (int i = 1; i < bins_array.Count; i++)
                bins[i] = new Bin(bins_array[i - 1], bins_array[i]);
            bins[bins_array.Count] = new Bin(bins_array[bins_array.Count - 1], double.MaxValue);
            foreach (double value in values)
            {
                int index = Array.BinarySearch(bins, new Bin(value, value));
                result[index]++;
            }
            return result;
        }
     
        public static object FTest([WorksheetContext] IWorksheet worksheet, IArray array1, IArray array2)
        {
            List<double> values1 = ExtractValues(array1);
            List<double> values2 = ExtractValues(array2);
            double var1 = Statistician.Variance(values1);
            double var2 = Statistician.Variance(values2);
            if (var2 == 0)
                return Errors.DivError;
            double n2 = values2.Count ;
            double n1 = values1.Count ;


            double f;
            double df1;
            double df2;
            if (var1 > var2)
            {
                f = var1 / var2;
                df1 = n1 - 1;
                df2 = n2 - 1;
            }
            else
            {
                f = var2 / var1;
                df1 = n2 - 1;
                df2 = n1 - 1;
            }
            double prob = 2.0*Beta.BetaI(0.5*df2, 0.5*df1, df2/(df2 + df1*f));
            if (prob > 1.0) prob = 2.0 - prob;
            return prob;
        }

        [AddInFunction(Name = "F.TEST")]
        public static object F_Test([WorksheetContext] IWorksheet worksheet, IArray array1, IArray array2)
        {
            return FTest(worksheet, array1, array2);
        }

        [AddInFunction(Name = "GAMMA.DIST")]
        public static object Gamma_Dist(double x, double alpha, double beta, [TreatZeroEmptyAsFalse] bool cumulative)
        {
            return GammaDist(x, alpha, beta, cumulative);
        }

        public static object GammaDist(double x, double alpha, double beta, [TreatZeroEmptyAsFalse] bool cumulative)
        {
            if (x < 0 || alpha == 0 || beta == 0)
                return Errors.NumError;
            if (!GammaDistribution.IsValidParameters(alpha, beta))
                return Errors.NumError;
            var d = new GammaDistribution(alpha, beta);
            if (cumulative)
                return d.CumulativeProbability(x);
            return d.Probability(x);
        }

        [AddInFunction(Name = "GAMMA.INV")]
        public static object Gamma_Inv(double probability, double alpha, double beta)
        {
            return GammaInv(probability, alpha, beta);
        }

        public static object GammaInv(double probability, double alpha, double beta)
        {
            if (!Distribution.IsValidProbabilty(probability) || alpha == 0 || beta == 0)
                return Errors.NumError;
            return new GammaDistribution(alpha, beta).InverseCumulativeProbability(probability);
        }

        [AddInFunction(Name = "GAMMALN.PRECISE")]
        public static object GammaLn_Precise(double x)
        {
            return GammaLn(x);
        }

        public static object GammaLn(double x)
        {
            if (x <= 0)
                return Errors.NumError;
            return Statistician.LogGamma(x);
        }

        public static object GeoMean(
            [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreStrings(ReferenceOption.ReferenceOnly), IgnoreNulls,
             AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
             TreatBooleansAsDouble(ReferenceOption.ArgumentOnly)] params double[] number)
        {
            if (number.Length == 0)
                return Errors.NumError;
            double product = 1;
            double exp = 0;
            double f = Math.E*Math.E*Math.E*Math.E*Math.E*Math.E*Math.E*Math.E;
            foreach (double d in number)
            {
                if (d <= 0)
                    return Errors.NumError;
                product *= d;
                while (product > f)
                {
                    product /= f;
                    exp += 8;
                }
                while (product < 1)
                {
                    product *= f;
                    exp -= 8;
                }
            }

            return Math.Exp(((Math.Log(product) + exp)/number.Length));
        }

        public static object Growth([WorksheetContext]IWorksheet worksheet, IArray known_y, [Optional(null)]IArray known_x ,
            [Optional(null)]IArray new_x, [Optional(true)]bool @const)
        {
            List<double> ys;
            List<double> xs;
            if (known_x == null)
            {
                ys = ExtractValues(known_y);
                xs = new List<double>();
                for (int i = 0; i < ys.Count; i++)
                    xs.Add(i+1);
            }
            else
            {
                ExtractValues(known_y, known_x, out ys, out xs, worksheet.Workbook);
            }
            List<double> new_xs;
            if (new_x == null)
                new_xs = xs;
            else
            {
                new_xs = ExtractValues(new_x);
            }
            ys = new List<double>(Enumerable.Select(ys, y => Math.Log(y)));
            var regression = new SimpleRegression(xs, ys, @const?RegressionType.WithIntercept:RegressionType.WithoutIntercept);
            return new RowArrayValue(Enumerable.Select(new_xs, x => Math.Exp(regression.Intercept + regression.Slope * x)));
            //Math.Exp(regression.Slope);
        }

        [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreStrings(ReferenceOption.ReferenceOnly), IgnoreNulls,
         AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
         TreatBooleansAsDouble(ReferenceOption.ArgumentOnly)]
        public static object HarMean(params double[] numbers)
        {
            if (numbers.Length == 0)
                return Errors.NumError;
            double inverseSum = 0;
            foreach (double d in numbers)
            {
                if (d <= 0)
                    return Errors.NumError;
                inverseSum += 1/d;
            }
            return (numbers.Length)/inverseSum;
        }

        [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreStrings(ReferenceOption.ReferenceOnly), IgnoreNulls,
         AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
         TreatBooleansAsDouble(ReferenceOption.ArgumentOnly)]
        [AddInFunction(Name = "HYPGEOM.DIST")]
        public static object HypGeom_Dist(double sample_s, double number_sample, double population_s, double number_pop, bool cumulative)

        {
            var isample_s = (int)sample_s;
            var inumber_sample = (int)number_sample;
            var ipopulation_s = (int)population_s;
            var inumber_pop = (int)number_pop;
            if (sample_s < (inumber_sample - inumber_pop + ipopulation_s))
                return Errors.NumError;

            if (inumber_sample < 0) return Errors.NumError;
            if (inumber_sample > inumber_pop) return Errors.NumError;
            if (ipopulation_s < 0) return Errors.NumError;
            if (ipopulation_s > inumber_pop) return Errors.NumError;
            if (inumber_pop < 0) return Errors.NumError;

            var d = new HyperGeometricDistribution(inumber_pop, ipopulation_s, inumber_sample);
            if (cumulative)
                return d.CumulativeProbability(isample_s);
            else
                return d.Probability(isample_s);
        }

        [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreStrings(ReferenceOption.ReferenceOnly), IgnoreNulls,
         AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
         TreatBooleansAsDouble(ReferenceOption.ArgumentOnly)]
        public static object HypGeomDist(double sample_s, double number_sample, double population_s, double number_pop)
        {
            return HypGeom_Dist(sample_s, number_sample, population_s, number_pop, false);
/*            var isample_s = (int) sample_s;
            var inumber_sample = (int) number_sample;
            var ipopulation_s = (int) population_s;
            var inumber_pop = (int) number_pop;
            if (sample_s < (inumber_sample - inumber_pop + ipopulation_s))
                return Errors.NumError;

            if (inumber_sample < 0) return Errors.NumError;
            if (inumber_sample > inumber_pop) return Errors.NumError;
            if (ipopulation_s < 0) return Errors.NumError;
            if (ipopulation_s > inumber_pop) return Errors.NumError;
            if (inumber_pop < 0) return Errors.NumError;

            var d = new HyperGeometricDistribution(inumber_pop, ipopulation_s, inumber_sample);
            return d.Probability(isample_s);*/
        }

        [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreStrings(ReferenceOption.ReferenceOnly), IgnoreNulls,
         AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
         TreatBooleansAsDouble(ReferenceOption.ArgumentOnly)]
        public static object Intercept(IArray known_y, IArray known_x)
        {
            int yRowMult = 0;
            int yColumnMult = 0;
            int xRowMult = 0;
            int xColumnMult = 0;
            int len;
            if (known_y.ColumnCount > 1)
            {
                yColumnMult = 1;
                len = known_y.ColumnCount;
                if (known_y.RowCount > 1)
                    return Errors.NaError;
            }
            else
            {
                yRowMult = 1;
                len = known_y.RowCount;
            }

            if (known_x.ColumnCount > 1)
            {
                xColumnMult = 1;
                if (known_x.RowCount > 1)
                    return Errors.NaError;
                len = Math.Min(len, known_x.ColumnCount);
            }
            else
            {
                xRowMult = 1;
                len = Math.Min(len, known_x.RowCount);
            }
            var xx = new List<double>();
            var yy = new List<double>();
            for (int i = 0; i < len; i++)
            {
                object y = known_y[yRowMult*i, yColumnMult*i];
                object x = known_x[xRowMult*i, xColumnMult*i];
                if (x is double && y is double)
                {
                    xx.Add((double) x);
                    yy.Add((double) y);
                }
            }
            if (xx.Count < 2)
                return Errors.NaError;
            var r = new SimpleRegression(xx, yy, RegressionType.WithIntercept);
            return r.Intercept;
        }

        [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreStrings(ReferenceOption.ReferenceOnly), IgnoreNulls,
         AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
         TreatBooleansAsDouble(ReferenceOption.ArgumentOnly)]
        public static object Kurt(double[] numbers)
        {
            if (numbers.Length < 4)
                return Errors.DivError;
            double average = Statistician.Mean(numbers);
            double stdDev = Statistician.StandardDeviation(numbers);
            if (stdDev == 0)
                return Errors.DivError;
            double part2 = 0;
            for (int i = 0; i < numbers.Length; i++)
            {
                double dxi = (numbers[i] - average)/stdDev;
                dxi = dxi*dxi;
                dxi = dxi*dxi;
                part2 += dxi;
            }

            double denominator = (double) (numbers.Length - 2)*(numbers.Length - 3);
            double part1 = (double) numbers.Length*(numbers.Length + 1)/((numbers.Length - 1)*denominator);
            double part3 = 3*(numbers.Length - 1)*(numbers.Length - 1)/denominator;

            return part1*part2 - part3;
        }

        [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreStrings(ReferenceOption.ReferenceOnly), IgnoreNulls,
         AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
         TreatBooleansAsDouble(ReferenceOption.ArgumentOnly)]
        public static object Large(
            [TreatArrayValueColumnAsDoubleArray] double[] array,
            int k)
        {
            if (k <= 0 || k > array.Length || array.Length == 0)
                return Errors.NumError;
            Array.Sort(array);
            return array[array.Length - k];
        }

        public static object LinEst([WorksheetContext] IWorksheet worksheet, IArray known_y,
                                    [Optional(null)] IArray known_x, [Optional(true)] bool @const,
                                    [Optional(false)] bool stats)
        {
            List<double> ys;
            List<List<double>> xs;
            if (known_x == null)
            {
                ys = ExtractValues(known_y);
                xs = new List<List<double>>();
                for (int i = 0; i < ys.Count; i++)
                    xs.Add(new List<double>{i + 1});
            }
            else
            {
                ys = ExtractValues(known_y);
                xs = ExtractValues2(known_x);
                
            }
            var regression = new Regression( ys, xs, RegressionType.WithIntercept);
            var coefficients = new List<double>(regression.Coefficients);
            List<double> spreadsheetCoefficients = new List<double>();
            for (int i = 0; i < coefficients.Count - 1; i++)
                spreadsheetCoefficients.Add(coefficients[coefficients.Count - 2 - i]);
            spreadsheetCoefficients.Add(coefficients[coefficients.Count - 1]);
    //        if (known_y.ColumnCount == 1)
      //          return new RowArrayValue(spreadsheetCoefficients);
            return new ColumnArrayValue(spreadsheetCoefficients);
        }

        public static object LogEst([WorksheetContext] IWorksheet worksheet, IArray known_y,
                                    [Optional(null)] IArray known_x, [Optional(true)] bool @const,
                                    [Optional(false)] bool stats)
        {
            List<double> ys;
            List<List<double>> xs;
            RegressionType type;
            if (known_x == null)
            {
                ys = ExtractValues(known_y);
                xs = new List<List<double>>();
                for (int i = 0; i < ys.Count; i++)
                    xs.Add(new List<double>(i + 1));
                type = RegressionType.WithoutIntercept;
            }
            else
            {
                ys = ExtractValues(known_y);
                xs = ExtractValues2(known_x);
                type = RegressionType.WithIntercept;
            }
            ys = new List<double>(Enumerable.Select(ys, y => Math.Log(y)));
            var regression = new Regression(ys, xs, type);
            var coefficients = new List<double>(Enumerable.Select(regression.Coefficients, c => Math.Exp(c)));
            List<double> spreadsheetCoefficients = new List<double>();
            for (int i = 0; i < coefficients.Count - 1; i++)
                spreadsheetCoefficients.Add(coefficients[coefficients.Count - 2 - i]);
            spreadsheetCoefficients.Add(coefficients[coefficients.Count - 1]);
        //    if (known_y.ColumnCount == 1)
          //      return new RowArrayValue(spreadsheetCoefficients);

            return new ColumnArrayValue(spreadsheetCoefficients);

         //\\   return new ColumnArrayValue(Enumerable.Select(regression.Coefficients, c => Math.Exp(c)));
        }

        public static object LogInv(double probability, double mean, double standard_dev)
        {
            if (!Distribution.IsValidProbabilty(probability))
                return Errors.NumError;
            var d = new LogNormalDistribution(mean, standard_dev);
            return d.InverseCumulativeProbability(probability);
        }


        [AddInFunction(Name = "LOGNORM.INV")]
        public static double LogNorm_Inv(double probability, double mean, double standard_dev)
        {
            if (probability <= 0 || probability >= 1)
                throw new ErrorException(Errors.NumError);
            var d = new LogNormalDistribution(mean, standard_dev);
            return d.InverseCumulativeProbability(probability);
        }

        [AddInFunction(Name = "LOGNORM.DIST")]
        public static double LogNorm_Dist(double x, double mean, double standard_dev, bool cumulative)
        {
            if (x <= 0 || standard_dev <= 0)
                throw new ErrorException(Errors.NumError);
            var d = new LogNormalDistribution(mean, standard_dev);
            if (cumulative)
                return d.CumulativeProbability(x);
            return d.Probability(x);
        }

        public static double LogNormDist(double x, double mean, double standard_dev)
        {
            return LogNorm_Dist(x, mean, standard_dev, true);
        }

        [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreStrings(ReferenceOption.ReferenceOnly), IgnoreNulls,
         AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
         TreatBooleansAsDouble(ReferenceOption.ArgumentOnly)]
        public static object Median(double[] array)
        {
            return Percentile(array, 0.5);
        }

        [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreStrings(ReferenceOption.ReferenceOnly), IgnoreNulls,
         AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
         TreatBooleansAsDouble(ReferenceOption.ArgumentOnly)]
        public static object Mode(params double[] number)
        {
            var table = new Dictionary<double, int>();
            foreach (double d in number)
            {
                int i;
                if (table.TryGetValue(d, out i))
                {
                    table.Remove(d);
                }
                i++;
                table.Add(d, i);
            }
            int max = 0;
            double result = 0;
            foreach (var p in table)
            {
                if (p.Value > max)
                {
                    max = p.Value;
                    result = p.Key;
                }
            }
            if (max <= 1)
                return Errors.NumError;
            return result;
        }

        [AddInFunction(Name = "NEGBINOM.DIST")]
        public static object NegBinom_Dist(double number_f, double number_s, double probability_s, bool cumulative)
        {
            var failureCount = (int)Math.Floor(number_f);
            var successCount = (int)Math.Floor(number_s);
            if (!Distribution.IsValidProbabilty(probability_s))
                return Errors.NumError;
            if (failureCount <= 0 || successCount <= 0)
                return Errors.NumError;
            if (failureCount + successCount - 1 == 0)
                return Errors.NumError;
            var p1 = probability_s;
            var d = new BinomialDistribution(failureCount + successCount, p1);
            double p2;
            if (cumulative)
            {
                throw new NotImplementedException();
                p2 = d.CumulativeProbability(successCount);
            }
            else p2 = d.Probability(successCount);
            p1 = ((double)successCount) / (successCount + failureCount);
            return (p1 * p2);
        }

        public static object NegBinomDist(double number_f, double number_s, double probability_s)
        {
            return NegBinom_Dist(number_f, number_s, probability_s, false);
        }


        [AddInFunction(Name = "NORM.DIST")]
        public static double Norm_Dist(double x, double mean, double standard_dev,
                                       [TreatZeroEmptyAsFalse] [Optional(false)] bool cumulative)
        {
            return NormDist(x, mean, standard_dev, cumulative);
        }

        public static double NormDist(double x, double mean, double standard_dev,
                                      [TreatZeroEmptyAsFalse] [Optional(false)] bool cumulative)
        {
            var d = new NormalDistribution(mean, standard_dev);
            if (cumulative)
                return d.CumulativeProbability(x);
            else
                return d.Probability(x);
        }

        [AddInFunction(Name = "NORM.INV")]
        public static object Norm_Inv(double probability, double mean, double standard_dev)
        {
            return NormInv(probability, mean, standard_dev);
        }

        public static object NormInv(double probability, double mean, double standard_dev)
        {
            if (!Distribution.IsValidProbabilty(probability))
                return Errors.NumError;
            var d = new NormalDistribution(mean, standard_dev);
            return d.InverseCumulativeProbability(probability);
        }


        [AddInFunction(Name = "NORM.S.DIST")]
        public static double NormS_Dist(double z)
        {
            throw new NotImplementedException();
            return NormSDist(z);
        }

        public static double NormSDist(double z)
        {
            if (z < 0)
                return 1 - NormSDist(-z);
            return NormDist(z, 0, 1, true); //\\?
        }


        [AddInFunction(Name = "NORM.S.INV")]
        public static object Norm_S_Inv(double probability)
        {
            return NormSInv(probability);
        }

        public static object NormSInv(double probability)
        {
            if (!Distribution.IsValidProbabilty(probability))
                return Errors.NumError;
            return NormInv(probability, 0, 1);
        }

        [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreStrings(ReferenceOption.ReferenceOnly), IgnoreNulls,
         AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
         TreatBooleansAsDouble(ReferenceOption.ArgumentOnly),
         TreatArrayValueColumnAsDoubleArray]
        public static object Pearson(double[] array1, double[] array2)
        {
            if (array1.Length*array2.Length == 0)
                return Errors.NumError;
            double s1 = Math.Sqrt(VarP(array1));
            double s2 = Math.Sqrt(VarP(array2));
            if (s1*s2 == 0)
                return Errors.NumError;
            double var = Statistician.Covariance(array1, array2);
            return var/s1/s2;
        }

        [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreStrings(ReferenceOption.ReferenceOnly), IgnoreNulls,
         AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
         TreatBooleansAsDouble(ReferenceOption.ArgumentOnly)]
        public static object Percentile(double[] array, double k)
        {
            if (k < 0 || k > 1 || array.Length == 0)
                return Errors.NumError;
            Array.Sort(array);
            double d = (array.Length - 1)*k;
            var i = (int) d;
            double remainder = d - i;

            if (remainder == 0.0 || i + 1 >= array.Length)
                return array[i];
            else
                return (1 - remainder)*array[i] + remainder*array[i + 1];
        }

        public static object PercentRank(IArray array, double x, [Optional(3)] int significance)
        {
            List<double> l = ExtractValues(array);
#if DEBUG
            double d = PercentRankImp(new List<double> { 1, 2, 3, 6, 6, 6, 7, 8, 9 }, 6, significance);
            Contract.Assert((d == 0.375));
#endif
            double result = PercentRankImp(l, x, significance);
            return result;
        }

        private static double PercentRankImp(List<double> array, double x, int significance)
        {
            double result;
            int smallerCount, largerCount;
            int n = array.Count;
            int equalCount = smallerCount = largerCount = 0;
            double xLarger = double.MaxValue;
            double xSmaller = double.MinValue;
            foreach (var value in array)
            {
                switch (value.CompareTo(x))
                {
                    case -1:
                        {
                            if (smallerCount == 0 || xSmaller < value)
                                xSmaller = value;
                            xSmaller = Math.Max(xSmaller, value);
                            smallerCount++;
                        }
                        break;
                    case 1:
                        {
                            if (largerCount == 0 || xLarger > value)
                                xLarger = value;
                            xLarger = Math.Min(xLarger, value);
                            largerCount++;
                        }
                        break;
                    case 0:
                        equalCount++;
                        break;
                    default:
                        throw new NotSupportedException();
                }
            }
            if (smallerCount + equalCount == 0 || largerCount + equalCount == 0)
                throw new ErrorException(Errors.NaError);
            if (n == 1)
                result = 1;
            else
            {
                if (equalCount > 0)
                    result = smallerCount / (double)(n - 1);
                else
                {
                    double d1 = (smallerCount - 1) / (double)(n - 1);
                    double d2 = smallerCount / (double)(n - 1);
                    result = (d1 * (xLarger - x) +
                         d2 * (x - xSmaller)) / (xLarger - xSmaller);
                }
                if (significance < 0)
                    throw new ErrorException(Errors.NumError);

                double d3 = Math.Pow(10, -significance);
                if (d3 <= 0)
                    throw new ErrorException(Errors.DivError);
                result = Truncate(result / d3) * d3;
            }
            return result;
        }

        private static double Truncate(double d)
        {
            return d >= 0 ? Math.Floor(d) : -Math.Floor(-d);
        }

        public static object Permut(double number, double number_chosen)
        {
            var n = (int) number;
            var k = (int) number_chosen;
            if (0 <= k && k <= n)
            {
                double result = 1;
                for (int i = n - k + 1; i <= n; i++)
                    result *= i;
                return result;
            }
            return Errors.NumError;
        }

        [TreatNullsAsZero]
        [AddInFunction(Name = "POISSON.DIST")]
        public static object Poisson_Dist(double x, double mean, [TreatZeroEmptyAsFalse] bool cumulative)
        {
            return Poisson(x, mean, cumulative);
        }

        [TreatNullsAsZero]
        public static object Poisson(double x, double mean, [TreatZeroEmptyAsFalse] bool cumulative)
        {
            if (mean < 0)
                return Errors.NumError;
            var xx = (int) x;
            if (xx < 0)
                return Errors.NumError;
            if (cumulative)
                return new PoissonDistribution(mean).CumulativeProbability(xx);
            else
                return new PoissonDistribution(mean).Probability(xx);
        }

        public static object Prob([WorksheetContext] IWorksheet worksheet, IArray x_range, IArray prob_range,
                                  [Optional(0d)] double lower_limit,
                                  [Optional(null)] double? upper_limit)
        {
            List<double> xs;
            List<double> probs;
            double upper = upper_limit.GetValueOrDefault(lower_limit);
            ExtractValues(x_range, prob_range, out xs, out probs, worksheet.Workbook);
            double totalSum = 0;
            double sum = 0;
            for (int i = 0; i < xs.Count; i++)
            {
                double x = xs[i];
                double prob = probs[i];
                if (prob <= 0 || prob > 1)
                {
                    throw new ErrorException(Errors.NumError);
                }
                totalSum += prob;
                if (x >= lower_limit && x <= upper)
                    sum += prob;
            }

            if (Math.Abs(totalSum - 1) > xs.Count*2*Epsilon)
            {
                throw new ErrorException(Errors.NumError);
            }

            return sum;
        }

        [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreStrings(ReferenceOption.ReferenceOnly), IgnoreNulls,
         AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
         TreatBooleansAsDouble(ReferenceOption.ArgumentOnly)]
        public static object Quartile(
            [TreatArrayValueColumnAsDoubleArray] double[] array,
            int k
            )
        {
            return Percentile(array, k/4d);
        }

        public static object RSQ([WorksheetContext] IWorksheet worksheet, IArray known_y, IArray known_x)
        {
            List<double> xs;
            List<double> ys;
            ExtractValues(known_y, known_x, out ys, out xs, worksheet.Workbook);
            if (xs.Count != ys.Count)
                return Errors.ValueError;
            var regression = new SimpleRegression(xs, ys, RegressionType.WithIntercept);
            return regression.RSQ;
        }

        [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreStrings(ReferenceOption.ReferenceOnly), IgnoreNulls,
         AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
         TreatBooleansAsDouble(ReferenceOption.ArgumentOnly)]
        public static object Skew(double[] number)
        {
            if (number.Length < 3)
                return Errors.NumError;
            double m = Statistician.Mean(number);
            double s = Math.Sqrt(Statistician.Variance(number));
            if (s == 0)
                return Errors.NumError;
            double sum = 0;
            foreach (double dbl in number)
            {
                double dxi = (dbl - m)/s;
                sum += dxi*dxi*dxi;
            }
            return ((sum*number.Length)/(number.Length - 1))/(number.Length - 2);
        }

        [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreStrings(ReferenceOption.ReferenceOnly), IgnoreNulls,
         AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
         TreatBooleansAsDouble(ReferenceOption.ArgumentOnly)]
        public static object Slope(IArray known_y, IArray known_x)
        {
            int yRowMult = 0;
            int yColumnMult = 0;
            int xRowMult = 0;
            int xColumnMult = 0;
            int len;
            if (known_y.ColumnCount > 1)
            {
                yColumnMult = 1;
                len = known_y.ColumnCount;
                if (known_y.RowCount > 1)
                    return Errors.NaError;
            }
            else
            {
                yRowMult = 1;
                len = known_y.RowCount;
            }

            if (known_x.ColumnCount > 1)
            {
                xColumnMult = 1;
                if (known_x.RowCount > 1)
                    return Errors.NaError;
                len = Math.Min(len, known_x.ColumnCount);
            }
            else
            {
                xRowMult = 1;
                len = Math.Min(len, known_x.RowCount);
            }
            var xx = new List<double>();
            var yy = new List<double>();
            for (int i = 0; i < len; i++)
            {
                object y = known_y[yRowMult*i, yColumnMult*i];
                object x = known_x[xRowMult*i, xColumnMult*i];
                if (x is double && y is double)
                {
                    xx.Add((double) x);
                    yy.Add((double) y);
                }
            }
            if (xx.Count < 2)
                return Errors.NaError;
            var r = new SimpleRegression(xx, yy, RegressionType.WithIntercept);
            return r.Slope;
        }

        [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreStrings(ReferenceOption.ReferenceOnly), IgnoreNulls,
         AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
         TreatBooleansAsDouble(ReferenceOption.ArgumentOnly)]
        public static object Small(
            [TreatArrayValueColumnAsDoubleArray] double[] array,
            int k
            )
        {
            k--;
            if (k < 0 || k >= array.Length)
                return Errors.NumError;
            Array.Sort(array);
            return array[k];
        }

        public static object Standardize(double x, double mean, double standard_dev)
        {
            if (standard_dev == 0)
                return Errors.NumError;
            return (x - mean)/standard_dev;
        }

        public static object StDev(
            [IgnoreStrings(ReferenceOption.ArgumentOrReference), IgnoreBooleans(ReferenceOption.ArgumentOrReference),
             IgnoreNulls] params double[] numbers)
        {
            if (numbers.Length < 2)
                return Errors.DivError;
            object result = Var(numbers);
            if (result is double)
                return Math.Sqrt((double) result);
            return result;
        }

        [TreatStringsAsZero(ReferenceOption.ReferenceOnly), IgnoreNulls,
         AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
         TreatBooleansAsDouble]
        public static object StDevA(params
                                        double[] numbers)
        {
            return StDev(numbers);
        }

        public static object StDevP(
            [IgnoreStrings(ReferenceOption.ArgumentOrReference), IgnoreBooleans(ReferenceOption.ArgumentOrReference),
             IgnoreNulls] params double[] numbers)
        {
            if (numbers.Length < 1)
                return Errors.DivError;

            object result = VarP(numbers);
            if (result is double)
                return Math.Sqrt((double) result);
            return result;
        }

        [TreatStringsAsZero(ReferenceOption.ReferenceOnly), IgnoreNulls,
         AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
         TreatBooleansAsDouble]
        public static object StDevPA(params
                                         double[] numbers)
        {
            return StDevP(numbers);
        }

        public static object STEYX([TreatArrayValueColumnAsDoubleArray] double[] known_y,
                                   [TreatArrayValueColumnAsDoubleArray] double[] known_x)
        {
            if (known_y.Length == 0 || known_x.Length != known_y.Length)
                return Errors.NaError;
            double sumX = 0;
            double sumY = 0;
            double sumXX = 0;
            double sumYY = 0;
            double sumXY = 0;
            int len = known_x.Length;
            for (int i = 0; i < len; i++)
            {
                double x = known_x[i];
                double y = known_y[i];
                sumX += x;
                sumY += y;
                sumXX += x*x;
                sumYY += y*y;
                sumXY += x*y;
            }
            double num = len*sumXY - sumX*sumY;
            num *= num;
            double denominator = len*sumXX - sumX*sumX;
            if (denominator == 0)
                return Errors.NumError;

            return Math.Sqrt((len*sumYY - sumY*sumY - num/denominator)/(len*(len - 2)));
        }

        [TreatNullsAsZero]
        public static object TDist(double x, double deg_freedom, int tails)
        {
            if (x < 0)
                return Errors.NumError;
            switch (tails)
            {
                case 1:
                case 2:
                    return tails*(1 - new TDistribution(deg_freedom).CumulativeProbability(x));
                default:
                    return Errors.NumError;
            }
        }

        public static object TInv(double probability, int degrees_freedom)
        {
            if (!Distribution.IsValidProbabilty(probability))
                return Errors.NumError;
            if (degrees_freedom < 1)
                return Errors.NumError;
            double p = (1 - probability/2);
            return new TDistribution(degrees_freedom).InverseCumulativeProbability(p);
        }

        public static object Trend([WorksheetContext]IWorksheet worksheet, IArray known_ys, [Optional(null)]IArray known_xs, [Optional(null)]IArray new_xs, [Optional(true)]bool @const)
        {
            List<double> ys;
            List<double> xs;
            if (known_xs == null)
            {
                ys = ExtractValues(known_ys);
                xs = new List<double>();
                for (int i = 0; i < ys.Count; i++)
                    xs.Add(i + 1);
            }
            else
                ExtractValues(known_ys, known_xs, out ys, out xs, worksheet.Workbook);
            List<double> newXList;
            if (new_xs == null)
            {
                newXList = xs;
            }
            else
            {
                newXList = ExtractValues(new_xs);
            }
            SimpleRegression regression = new SimpleRegression(xs, ys,
                                                               @const ? RegressionType.WithIntercept : RegressionType.WithoutIntercept);
            var newYs = Enumerable.Select(newXList, x => regression.Slope*x + regression.Intercept);
            if (known_ys.ColumnCount == 1)
                return new RowArrayValue(newYs);
            return new ColumnArrayValue(newYs);
        }

        public static object TrimMean(
            [TreatArrayValueColumnAsDoubleArray,
             IgnoreStrings,
             IgnoreBooleans,
             IgnoreNulls] double[] array,
            double percent
            )
        {
            if (percent < 0 || percent > 1)
                return Errors.NumError;
            var toRemove = (int) (array.Length*percent/2);
            double sum = 0;
            Array.Sort(array);
            for (int i = toRemove; i < array.Length - toRemove; i++)
            {
                sum += array[i];
            }
            return sum/(array.Length - 2*toRemove);
        }

        public static object TTest([IgnoreBooleans, IgnoreNulls, IgnoreStrings] double[] array1,
                                   [IgnoreBooleans, IgnoreNulls, IgnoreStrings] double[] array2, int tails, int type)
        {
            if (tails < 1 || tails > 2)
                return Errors.NumError;
            if (type < 1 || type > 3)
                return Errors.NumError;
            if (type == 1 && array1.Length != array2.Length)
                return Errors.NaError;
            var tTest = new TTest();
            double mul = (double) tails/2;
            switch (type)
            {
                case 1:
                    return tTest.PairedTTest(array1, array2)*mul;
                case 2:
                    return tTest.HomoScedasticTTest(array1, array2)*mul;
                case 3:
                    return tTest.HeteroScedasticTTest(array1, array2)*mul;
                default:
                    throw new NotSupportedException();
            }
        }

        [
            AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
            TreatBooleansAsDouble(ReferenceOption.ArgumentOnly),
            IgnoreStrings(ReferenceOption.ArgumentOrReference), IgnoreBooleans(ReferenceOption.ReferenceOnly),
            IgnoreNulls]
        [AddInFunction(Name = "VAR.P")]
        public static object Var_p(
            params double[] numbers)
        {
            throw new NotImplementedException();
            return Var(numbers);
        }

        [
            AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
            TreatBooleansAsDouble(ReferenceOption.ArgumentOnly),
            IgnoreStrings(ReferenceOption.ArgumentOrReference), IgnoreBooleans(ReferenceOption.ReferenceOnly),
            IgnoreNulls]
        public static object Var(
            params double[] numbers)
        {
            double average = Statistician.Mean(numbers);
            double result = 0;
            foreach (double dbl in numbers)
            {
                double diff = dbl - average;
                result += diff*diff;
            }
            return result/(numbers.Length - 1);
        }

        [
            AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
            TreatBooleansAsDouble,
            TreatStringsAsZero(ReferenceOption.ReferenceOnly),
            TreatNullsAsZero]
        [AddInFunction(Name = "VAR.S")]
        public static object Var_S(
            params
                double[] numbers)
        {
            return VarA(numbers);
        }

        [
            AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
            TreatBooleansAsDouble,
            TreatStringsAsZero(ReferenceOption.ReferenceOnly),
            TreatNullsAsZero]
        public static object VarA(
            params
                double[] numbers)
        {
            return Var(numbers);
        }

        [
            AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
            TreatBooleansAsDouble(ReferenceOption.ArgumentOnly),
            IgnoreStrings(ReferenceOption.ArgumentOrReference), IgnoreBooleans(ReferenceOption.ReferenceOnly),
            IgnoreNulls]
        public static double VarP(
            params double[] numbers)
        {
            double average = Statistician.Mean(numbers);
            double result = 0;
            foreach (double dbl in numbers)
            {
                double diff = dbl - average;
                result += diff*diff;
            }
            return result/(numbers.Length);
        }

        [
            AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
            TreatBooleansAsDouble,
            TreatStringsAsZero(ReferenceOption.ReferenceOnly),
            TreatNullsAsZero]
        public static object VarPA(
            double[] numbers)
        {
            if (numbers.Length < 1)
                return Errors.DivError;
            return VarP(numbers);
        }

        [AddInFunction(Name="WEIBULL.DIST")]
        public static object Weibull_Dist(double x, double alpha, double beta, [TreatZeroEmptyAsFalse] bool cumulative)
        {
            return Weibull(x, alpha, beta, cumulative);
        }
        public static object Weibull(double x, double alpha, double beta, [TreatZeroEmptyAsFalse] bool cumulative)
        {
            if (x < 0 || alpha == 0 || beta == 0)
                return Errors.NumError;
            var d = new WeibullDistribution(alpha, beta);
            if (cumulative)
                return d.CumulativeProbability(x);
            return d.Probability(x);
        }

        [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreStrings(ReferenceOption.ReferenceOnly),
         TreatBooleansAsDouble(ReferenceOption.ArgumentOnly),
         AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly)]
        [AddInFunction(Name = "Z.TEST")]
        public static object Z_Test(double[] array, double mu, [Optional(null)] double? sigma)
        {
            return ZTest(array, mu, sigma);
        }

        [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreStrings(ReferenceOption.ReferenceOnly),
         TreatBooleansAsDouble(ReferenceOption.ArgumentOnly),
         AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly)]
        public static object ZTest(double[] array, double mu, [Optional(null)] double? sigma)
        {
            double x_ = Statistician.Mean(array);
            int n = array.Length;
            if (sigma == null)
                sigma = Statistician.StandardDeviation(array);
            if (sigma.Value <= 0)
                return Errors.NumError;
            double val = (x_ - mu)/(sigma.Value/Math.Sqrt(n));
            object o = NormSDist(val);
            if (o is double)
                return 1 - (double) o;
            return o;
        }

        public static object Average(
            [IgnoreStrings(ReferenceOption.ReferenceOnly), IgnoreBooleans(ReferenceOption.ReferenceOnly),
             AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
             TreatBooleansAsDouble(ReferenceOption.ArgumentOnly),
             IgnoreNulls] params double[] number)
        {
            if (number.Length == 0)
                return Errors.DivError;
            return Statistician.Mean(number);
        }
        
        public static object AverageA([TreatStringsAsZero(ReferenceOption.ReferenceOnly),
                                       TreatStringsAsError(ReferenceOption.ArgumentOnly),
                                       TreatBooleansAsDouble(ReferenceOption.ArgumentOrReference), IgnoreNulls,
                                       IgnoreEmptyStrings] params
                                          double[] numbers)
        {
            return Average(numbers);
        }

        [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreStrings(ReferenceOption.ReferenceOnly),
         TreatBooleansAsDouble(ReferenceOption.ArgumentOnly),
         AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly)]
        public static object AveDev(params double[] number)
        {
            if (number.Length == 0)
                return Errors.DivError;
            double average = Statistician.Mean(number);
            double result = 0;
            foreach (double d in number)
            {
                result += Math.Abs(d - average);
            }
            return result/number.Length;
        }

        public static object Covar(double[] array1, double[] array2)
        {
            if (array1.Length != array2.Length)
                return Errors.NaError;
            return Statistician.Covariance(array1, array2);
        }

        public static object AverageIf([WorksheetContext] IWorksheet worksheet, IRange range2, string criteria,
                                       [Optional(null)] IRange average_range)
        {
            IRange valueRange = average_range;
            if (valueRange == null)
                valueRange = range2;
            // Criteria criteria1 = new Criteria(criteria);
            var rangeAndCriteria = new RangeAndCriteria(worksheet.Workbook, range2, criteria, valueRange);
            double result = 0;
            IParameterConverter converter = CompositeArgumentConverter.ToDoubleConverter();
            int count = 0;
            foreach (double v in rangeAndCriteria.GetDoubleMatches())
            {
                result += v;
                count++;
            }
            if (count == 0)
                return Errors.DivError;
            result = result/count;
            return result;
        }

        public static object AverageIfs([WorksheetContext] IWorksheet worksheet, IRange average_range,
                                        IRange criteria_range1, string criteria1,
                                        [NoConversion] params object[] additionalParameters)
        {
            if (additionalParameters.Length%2 != 0)
                return Errors.ValueError;
            var rangeAndCriterias = new List<RangeAndCriteria>();
            rangeAndCriterias.Add(new RangeAndCriteria(worksheet.Workbook, criteria_range1, criteria1, average_range));
            for (int i = 0; i < additionalParameters.Length;)
            {
                var r = (IRange) additionalParameters[i];
                i++;
                object o = additionalParameters[i];
                if (o is ICell)
                    o = ((ICell) o).Value;
                rangeAndCriterias.Add(new RangeAndCriteria(worksheet.Workbook, r, (string) o, average_range));
                i++;
            }
            var counts = new Dictionary<ICell, int>();
            foreach (RangeAndCriteria rangeandC in rangeAndCriterias)
            {
                foreach (ICell cell in rangeandC.GetMaches())
                {
                    if (!counts.ContainsKey(cell))
                        counts.Add(cell, 0);
                    counts[cell] = counts[cell] + 1;
                }
            }
            IParameterConverter converter = CompositeArgumentConverter.ToDoubleConverter();
            int count = 0;
            double result = 0;
            foreach (var pair in counts)
            {
                if (pair.Value == rangeAndCriterias.Count)
                {
                    object v = pair.Key.Value;
                    object res;
                    double? dbl = null;
                    if (v is double)
                        dbl = (double) v;
                    var cd = new ConversionData(average_range.Worksheet.Workbook, average_range.Worksheet, v,
                                                typeof (double));
                    converter.Convert(cd);
                    if (cd.Successful)
                    {
                        if (cd.Error != null)
                            return cd.Error;
                        dbl = (double) cd.Result;
                    }
                    result += dbl.Value;
                    count++;
                }
            }
            if (count == 0)
                return Errors.DivError;
            return result/count;
        }

        public static object Rank([WorksheetContext] IWorksheet context, double number, IRange @ref,
                                  [Optional(0)] int order)
        {
            var doubleValues = new List<double>();
            foreach (ICell cell in @ref.GetNonEmptyCells())
            {
                object v = cell.Value;
                if (v is int)
                    doubleValues.Add((int) v);
                if (v is double)
                    doubleValues.Add((double) v);
                if (v is DateTime)
                    doubleValues.Add(context.ChangeType<double>(v));
            }
            doubleValues.Sort();
            if (order == 0)
                doubleValues.Reverse();
            int result = doubleValues.IndexOf(number);
            if (result >= 0)
                return result + 1;
            return Errors.NaError;
        }

        public static int Count(params object[] values)
        {
            return CountImp(values);
        }

        internal static int CountImp(IEnumerable<object> values)
        {
            int result = 0;
            foreach (object o in values)
            {
                if (o is double)
                    result++;
                if (o is DateTime)
                    result++;
            }
            return result;
        }
        [AccessCellValue(AcceptNull = true)]
        public static int CountA(params object[] values)
        {
            int result = 0;
            foreach (object o in values)
            {
                if (o != null)
                    result++;
            }
            return result;
        }

        [TreatRangeAsEnumerableObjectAttribute]
        public static double CountIf([WorksheetContext] IWorksheet worksheet, IEnumerable<object> range, string criteria)
        {
            var c = new Criteria(worksheet.Workbook, criteria);
            double result = 0;
            foreach (object  o in range)
            {
                if (c.Matches(o))
                    result++;
            }
            return result;
        }

        public static object CountIfs([WorksheetContext] IWorksheet worksheet, IRange criteria_range1, string criteria1,
                                      [NoConversion] params object[] additionalParameters)
        {
            if (additionalParameters.Length%2 != 0)
                return Errors.ValueError;
            var rangeAndCriterias = new List<RangeAndCriteria>();
            rangeAndCriterias.Add(new RangeAndCriteria(worksheet.Workbook, criteria_range1, criteria1, criteria_range1));
            for (int i = 0; i < additionalParameters.Length;)
            {
                var r = (IRange) additionalParameters[i];
                i++;
                object o = additionalParameters[i];
                if (o is ICell)
                    o = ((ICell) o).Value;
                rangeAndCriterias.Add(new RangeAndCriteria(worksheet.Workbook, r, (string) o, criteria_range1));
                i++;
            }
            var counts = new Dictionary<ICell, int>();
            foreach (RangeAndCriteria rangeandC in rangeAndCriterias)
            {
                foreach (ICell cell in rangeandC.GetMaches())
                {
                    if (!counts.ContainsKey(cell))
                        counts.Add(cell, 0);
                    counts[cell] = counts[cell] + 1;
                }
            }
            int count = 0;
            double result = 0;
            foreach (var pair in counts)
            {
                if (pair.Value == rangeAndCriterias.Count)
                {
                    object v = pair.Key.Value;
                    object res;
                    double? dbl = null;
                    //  if (v is double)
                    //      dbl = (double)v;
                    //  result += dbl.Value;
                    count++;
                }
            }
            return count;
        }


        public static double CountBlank([AllowCellToRangeConversion] IRange range)
        {
            int count = 0;
            foreach (ICell cell in range.GetNonEmptyCells())
            {
                object value = cell.Value;
                if (value == null)
                    continue;
                if (value is string)
                {
                    var s = (string) value;
                    if (s.Length == 0)
                        continue;
                }
                count++;
            }
            return range.RowSpan*range.ColumnSpan - count;
        }

        public static double Min([IgnoreBooleans(ReferenceOption.ReferenceOnly),
                                  IgnoreStrings(ReferenceOption.ReferenceOnly), IgnoreNulls,
                                  TreatBooleansAsDoubleAttribute(ReferenceOption.ArgumentOnly),
                                  AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly),
                                 ] params double[] numbers)
        {
            if (numbers.Length == 0)
                return 0;
            double result = numbers[0];
            foreach (double d in numbers)
                result = Math.Min(result, d);
            return result;
        }

        public static double MinA([
            TreatBooleansAsDouble,
            TreatStringsAsZero(ReferenceOption.ReferenceOnly),
            AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly), IgnoreNulls] params double[] numbers)
        {
            return Min(numbers);
        }

        public static double Max(
            [IgnoreBooleans(ReferenceOption.ReferenceOnly), IgnoreStrings(ReferenceOption.ReferenceOnly), IgnoreNulls,
             TreatBooleansAsDouble(ReferenceOption.ArgumentOnly),
             AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly)
            ] params double[] numbers)
        {
            if (numbers.Length == 0)
                return 0;
            double result = numbers[0];
            foreach (double d in numbers)
                result = Math.Max(result, d);
            return result;
        }

        public static double MaxA([
            TreatBooleansAsDouble,
            TreatStringsAsZero(ReferenceOption.ReferenceOnly),
            AllowStringToDoubleConversion(ReferenceOption.ArgumentOnly), IgnoreNulls] params double[] numbers)
        {
            return Max(numbers);
        }

        #region Nested type: Bin

        private struct Bin : IComparable<Bin>
        {
            private readonly double _maxValue;
            private readonly double _minValue;

            public Bin(double minValue, double maxValue)
            {
                _minValue = minValue;
                _maxValue = maxValue;
            }

            #region IComparable<Bin> Members

            public int CompareTo(Bin other)
            {
                if (other._minValue != other._maxValue)
                    return -other.CompareTo(this);
                Contract.Assert(other._minValue == other._maxValue);
                return Compare(other._minValue);
            }

            #endregion

            public int Compare(double value)
            {
                if (value <= _minValue)
                    return +1;
                if (value > _maxValue)
                    return -1;
                return 0;
            }

            public override string ToString()
            {
                return string.Format("[{0},{1}]", _minValue, _maxValue);
            }
        }

        #endregion

        #region Nested type: EnumerableHelper

        private class Enumerable
        {
            public static IEnumerable<T2> Select<T, T2>(IEnumerable<T> source, Func<T, T2> f)
            {
                foreach (T item in source)
                    yield return f
                        (item);
            }
        }

       

        #endregion

        #region Nested type: RangeAndCriteria

        #endregion
    }

    delegate T2 Func<T, T2>(T t);
    delegate T3 Func<T, T2, T3>(T t, T2 t2);
}

// ReSharper restore UnusedMember.Global